﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Wei8808Cms.Model;
using Wei8808Cms.Utility;

namespace Wei8808Cms.DAL
{
    public class NewTypeDAL : DbHelperBase
    {
        public int GetCount(Pagination qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM NewType WHERE 1=1 ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public DataSet GetPageList(Pagination qcmodel)
        {
            var ds = new DataSet();
            var strSql = new StringBuilder();
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by CreateTime) AS idx,[NewTypeId],[NewTypeName] FROM [NewType] ");
            strSql.Append(" WHERE 1=1 ");
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    ds = Db.ExecuteDataSet(cmd);
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return ds;
        }

        public bool Add(NewTypeInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [NewType]([NewTypeName],[CreateUser],[CreateTime]) ");
            strSql.Append(" VALUES(@NewTypeName,@CreateUser,@CreateTime) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NewTypeName", model.NewTypeName);
                    Db.AddInParameter(cmd, "@CreateUser", model.CreateUser);
                    Db.AddInParameter(cmd, "@CreateTime", model.CreateTime);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Delete(List<NewTypeInfo> list)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [NewType] WHERE NewTypeId=@NewTypeId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", DbType.String);
                    foreach (var i in list)
                    {
                        cmd.Parameters["@NewTypeId"].Value = i.NewTypeId;
                        Db.ExecuteNonQuery(cmd);
                    }
                    result = true;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public NewTypeInfo GetNewTypeInfo(int id)
        {
            var model = new NewTypeInfo();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [NewTypeId],[NewTypeName] FROM [NewType] where NewTypeId=@NewTypeId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NewTypeId", id);
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["NewTypeId"] != DBNull.Value)
                                model.NewTypeId = Convert.ToInt32(reader["NewTypeId"]);
                            model.NewTypeName = reader["NewTypeName"] == obj ? null : Convert.ToString(reader["NewTypeName"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool Update(NewTypeInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [NewType] SET [NewTypeName] = @NewTypeName,[ModifyUser] = @ModifyUser,[ModifyTime] = @ModifyTime WHERE [NewTypeId] = @NewTypeId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NewTypeName", model.NewTypeName);
                    Db.AddInParameter(cmd, "@ModifyUser", model.ModifyUser);
                    Db.AddInParameter(cmd, "@ModifyTime", model.ModifyTime);
                    Db.AddInParameter(cmd, "@NewTypeId", model.NewTypeId);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public List<NewTypeInfo> GetNewTypeList()
        {
            object obj = DBNull.Value;
            var list = new List<NewTypeInfo>();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [NewTypeId],[NewTypeName] FROM [NewType] Order by CreateTime ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new NewTypeInfo
                                            {
                                                NewTypeId = Convert.ToInt32(reader["NewTypeId"]),
                                                NewTypeName =
                                                    reader["NewTypeName"] == obj
                                                        ? null
                                                        : Convert.ToString(reader["NewTypeName"])
                                            };
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<NewTypeInfo> GetNewTypeAndNewsCountList()
        {
            object obj = DBNull.Value;
            var list = new List<NewTypeInfo>();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT * FROM ( ");
            strSql.Append(" SELECT A.NewTypeId,A.NewTypeName,COUNT(B.[NewId]) AS TotalCount FROM NewType AS A ");
            strSql.Append(" LEFT JOIN News AS B ON A.NewTypeId=B.NewTypeId ");
            strSql.Append(" GROUP BY A.NewTypeId,A.NewTypeName) AS T ORDER BY T.TotalCount DESC ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new NewTypeInfo
                            {
                                NewTypeId = Convert.ToInt32(reader["NewTypeId"]),
                                NewTypeName =
                                    reader["NewTypeName"] == obj
                                        ? null
                                        : Convert.ToString(reader["NewTypeName"]),
                                TotalCount = Convert.ToInt32(reader["TotalCount"])
                            };
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
